﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AugustSql
{
    public static class ExcelHelper
    {
        public static DataSet WorksheetToDataSet(string path)
        {
            DataSet dataSet = new DataSet();
            FileInfo file = new FileInfo(path);//数据表
            var package = new ExcelPackage(file);
            ExcelWorkbook workbook = package.Workbook;
            ExcelWorksheets excelWorksheets = workbook.Worksheets;
            foreach (ExcelWorksheet worksheet in excelWorksheets)
            {
                object[,] aa = (object[,])worksheet.Cells.Value;
                if (aa != null)
                {
                    DataTable dt = new DataTable();
                    for (int k = 0; k < aa.GetLength(1); k++)
                    {
                        if (aa[0, k] != null)
                            dt.Columns.Add(aa[0, k].ToString());
                    }

                    for (int i = 1; i < aa.GetLength(0); i++)
                    {
                        object[] obj = new object[dt.Columns.Count];
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            obj[j] = aa[i, j];
                        }
                        dt.Rows.Add(obj);
                    }
                    dataSet.Tables.Add(dt);
                }
            }
            return dataSet;
        }


        public static void DataTableSaveAsExcel(DataTable dataTable, string path)
        {
            DataTableSaveAsExcel(dataTable, path, null);
        }

        public static void DataTableSaveAsExcel(DataTable dataTable, string path, Dictionary<string, string> ParamUnit)
        {
            try
            {
                if (ParamUnit != null)
                {
                    for (int k = 0; k < dataTable.Columns.Count; k++)
                    {
                        if (ParamUnit.ContainsKey(dataTable.Columns[k].ColumnName))
                        {
                            dataTable.Columns[k].ColumnName = dataTable.Columns[k].ColumnName + "(" + ParamUnit[dataTable.Columns[k].ColumnName] + ")";
                        }
                    }
                }
                ExcelPackage ep = new ExcelPackage();
                ExcelWorkbook wb = ep.Workbook;
                ExcelWorksheet ws = wb.Worksheets.Add("result");
                ws.Cells["A1"].LoadFromDataTable(dataTable, true);//数据加载表中
                for (int i = 1; i <= dataTable.Columns.Count; i++)
                {
                    if (dataTable.Columns[i - 1].DataType == typeof(DateTime))
                    {
                        ws.Column(i).Style.Numberformat.Format = "yyyy/MM/dd HH:mm:ss";
                    }
                    else if (dataTable.Columns[i - 1].DataType == typeof(float))
                    {
                        ws.Column(i).Style.Numberformat.Format = "0.000";
                    }
                }
                int columnsNum = dataTable.Columns.Count;
                ws.Cells[1, 1, 1, columnsNum].AutoFilter = true;  //对第一行进行筛选
                ws.Protection.AllowAutoFilter = true;
                ws.Protection.AllowFormatCells = true;
                ws.Protection.AllowFormatColumns = true;
                ws.Protection.AllowFormatRows = true;
                ws.Protection.AllowInsertRows = true;
                ws.Protection.AllowInsertColumns = true;
                ws.Protection.AllowPivotTables = true;
                ws.Protection.AllowSelectLockedCells = true;
                ws.Protection.AllowSelectUnlockedCells = true;
                ws.Protection.AllowSort = true;
                ws.Protection.AllowPivotTables = true;
                ws.Protection.AllowEditScenarios = true;
                ws.Protection.AllowEditObject = true;
                ws.Protection.AllowEditScenarios = true;  //必须为true可实现筛选
                ws.Protection.AllowDeleteColumns = true;   //不允许删除列
                ws.Protection.AllowDeleteRows = true;  //不允许删除行
                ws.Cells[1000, 15].Style.Locked = true;   //锁定指定单元格（启用保护后默认锁定）
                ws.Column(1).Style.Locked = false;   //不锁定指定列（启用保护后默认锁定）
                ws.Row(1).Style.Locked = false;   //不锁定指定行（启用保护后默认锁定）
                ws.Protection.SetPassword("123");   //设置密码
                ws.Protection.IsProtected = true;   //启用保护

                //将数据保存到本地             
                ep.SaveAs(new FileInfo(path));
            }
            catch (Exception ex)
            {

            }

        }



        public static ExcelWorksheets OpenWorkSheet(string path)
        {
            FileInfo file = new FileInfo(path);//数据表
            DataTable dt = null;
            var package = new ExcelPackage(file);
            ExcelWorkbook workbook = package.Workbook;
            return workbook.Worksheets;
        }
    }
}
